using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
/// <summary>
/// Summary description for ShipperDAO
/// </summary>
public class ShipperDAO:DBAccess
{
    #region sql command
    string SQL_SELECT_ID = "select @@IDENTITY from Shippers";
    string SQL_SELECT_ALL = "select * from Shippers";
    string SQL_SELECT_BY_ID = "select * from Shippers where ShipperID=@id";
    string SQL_INSERT = "insert into Shippers(CompanyName,Phone) values(@companyName, @phone)";
    string SQL_UPDATE = "update Shippers set CompanyName=@companyName, Phone=@phone where ShipperID=@id";
    string SQL_DELETE = "delete from Shippers where ShipperID=@id";
    #endregion

    #region fields
    string message;
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion 

    public ShipperDAO()
	{
		//
		// TODO: Add constructor logic here
		//
    }
    #region methods
    public IList<ShipperInfo> GetAll()
    {
        IList<ShipperInfo> list = new List<ShipperInfo>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    ShipperInfo sh = new ShipperInfo();
                    sh.ID = dr.GetInt32(0);
                    sh.CompanyName = dr.GetString(1);
                    sh.Phone = dr.GetString(2);
                    list.Add(sh);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public ShipperInfo GetByID(int shipperId)
    {
        ShipperInfo sh = new ShipperInfo();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            command.Parameters.AddWithValue("@id",shipperId);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                if(dr.Read())
                {                  
                    sh.ID = dr.GetInt32(0);
                    sh.CompanyName = dr.GetString(1);
                    sh.Phone = dr.GetString(2);              
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return sh;
    }

    public int Insert(ShipperInfo sh)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@companyName",sh.CompanyName);
            command.Parameters.AddWithValue("@phone",sh.Phone);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = SQL_SELECT_ID;
                sh.ID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return effectedRows;
    }

    public int Update(ShipperInfo sh)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@companyName", sh.CompanyName);
            command.Parameters.AddWithValue("@phone", sh.Phone);
            command.Parameters.AddWithValue("@id", sh.ID);          
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();            
                Disconnect();
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return effectedRows;
    }

    public int Delete(ShipperInfo sh)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);   
            command.Parameters.AddWithValue("@id", sh.ID);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();        
                Disconnect();
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return effectedRows;
    }
    #endregion

}
